Mastering Web Site Development/Microsoft Mastering Web Site Development (Microsoft) (1997).iso/Labs/Lab08/Solution/enrollment.cls

Visual Basic class definition | 1997-04-24 | 5.0 KB | 167 lines

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Enrollment"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Const MAXCLASSES = 6
Function OverEnrolled(conn As ADODB.Connection, lngStudentID As Long) As Boolean
    Dim rsEnrollment As Object

    'Student cannot add if already at class limit
    strSQL = "select count(studentid) from enrollment where studentid = " & lngStudentID
    Set rsEnrollment = CreateObject("ADODB.recordset")
    rsEnrollment.ActiveConnection = conn
    rsEnrollment.Open strSQL, , adOpenKeyset
    If rsEnrollment.Fields(0).Value > MAXCLASSES Then
        OverEnrolled = True
    Else
        OverEnrolled = False
    End If
    rsEnrollment.Close
End Function
Function ClassCompleted(conn As ADODB.Connection, lngStudentID As Long, strClassID As String) As Boolean
    Dim rsEnrollment As Object

    'Student cannot drop a class if the class is completed
    'In other words, it has a grade
    strSQL = "select classid,studentid,grade from enrollment where studentid = " & lngStudentID & " AND classid ='" & strClassID & "'"
    Set rsEnrollment = conn.Execute(strSQL)

    'Check for no records even existing
    If rsEnrollment.BOF And rsEnrollment.EOF Then
       ClassCompleted = False
       Exit Function
    End If

    'Check for a non-null grade
    If Not IsNull(rsEnrollment.Fields("Grade")) Then
        ClassCompleted = True
    Else
        ClassCompleted = False
    End If
    rsEnrollment.Close
End Function
Public Function Add(ByVal lngStudentID As Long, ByVal strClassID As String) As Integer

    Dim conn As ADODB.Connection
    Dim strSQL As String

    'Set return value to 0
    Add = 0
    Set conn = CreateObject("ADODB.Connection")
    On Error GoTo ErrorHandler
    'Open connection with State University Database
    conn.Open "DSN=StateU;UID=sa;PWD=;"

    'Business rule
    'See if student is over enrolled
    If OverEnrolled(conn, lngStudentID) Then
        Add = 1
        Exit Function
    End If

    'Perform the work of adding the student to the class
    strSQL = "INSERT INTO enrollment (ClassID, StudentID) VALUES ('" & strClassID & "'," & lngStudentID & ")"
    conn.BeginTrans
    conn.Execute strSQL
    conn.CommitTrans

    'Close the connection
    conn.Close
    Exit Function

ErrorHandler:
    'Rollback the transaction if we got that far
    If Not IsEmpty(conn) Then conn.RollbackTrans

    'Raise an error to report back
    Add = 1
End Function

Public Function Transfer(ByVal lngStudentID As Long, ByVal strSrcClassID As String, ByVal strDstClassID As String) As Integer
    On Error GoTo ErrorHandler
    Dim conn As ADODB.Connection
    Dim strDropSQL, strAddSQL As String

    Transfer = 0
    Set conn = CreateObject("ADODB.connection")

    'Create update strings
    strDropSQL = "DELETE FROM enrollment where classid = '" & strSrcClassID & "' and studentid = " & lngStudentID
    strAddSQL = "INSERT INTO enrollment (ClassID, StudentID) VALUES ('" & strDstClassID & "'," & lngStudentID & ")"

    'Open connection with State University Database
    conn.Open "DSN=StateU;UID=sa;PWD=;"

    'Check that student is not dropping a class that
    'is already completed
    If ClassCompleted(conn, lngStudentID, strSrcClassID) Then
        Transfer = 1
        Exit Function
    End If

    'Perform work of transferring student
    conn.BeginTrans
    conn.Execute strDropSQL
    conn.Execute strAddSQL
    conn.CommitTrans

    'Close connection
    conn.Close
    Exit Function

ErrorHandler:
    'Rollback the transaction if we got that far
    If Not IsEmpty(conn) Then conn.RollbackTrans

    'Raise an error to report back
    Transfer = 1

End Function

Public Function Drop(ByVal lngStudentID As Long, _
ByVal strClassID As String) As Integer
    On Error GoTo ErrorHandler
    Dim conn As ADODB.Connection

    Dim strSQL As String

    Drop = 0
    Set conn = CreateObject("ADODB.connection")
    'Open connection with State University Database
    conn.Open "DSN=StateU;UID=sa;PWD=;"

    'Check business rule
    'Make sure class isn't already completed
    'In which case student cannot drop
    If ClassCompleted(conn, lngStudentID, strClassID) Then
        Drop = 1
        Exit Function
    End If

    'Perform the work of dropping the student from the class
    strSQL = "DELETE FROM enrollment where ClassID ='" & strClassID & "' AND Studentid =" & lngStudentID
    conn.BeginTrans
    conn.Execute strSQL
    conn.CommitTrans

    'Close the connection
    conn.Close
    Exit Function

ErrorHandler:
    'Rollback the transaction if we got that far
    If Not IsEmpty(conn) Then conn.RollbackTrans

    'Raise an error to report back
    Drop = 1
    'Check for student not enrolled to begin with 4

End Function